package com.tony.model;

import com.google.gson.Gson;
import com.tony.utils.DateUtil;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class Account {

    private long id;            //id字段
    private String phone;       //电话
    private String aid;         //投票的aid
    private String uid;         //投票的uid
    private String token;       //投票的token
    private String date;        //使用的日期



    private static String DB_PATH;

    private static Account instances = null;

    public static Account getInstances(String db_path){
        if(instances==null){
            instances=new Account();
        }
        DB_PATH=db_path;
        return instances;
    }

    public static void main(String[] args){
        List<Account> deviceInfo=Account.getInstances("G:\\desktop\\cctv.db").listData(100,"2018-08-30");
        System.out.println(new Gson().toJson(deviceInfo));
    }


    /**
     * 插入新数据到数据库
     * @param account   需要插入的账户信息
     * @return
     */
    public boolean insertData(Account account){
        try {
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:"+DB_PATH);
            Statement stmt = conn.createStatement();
            String sql="INSERT INTO tb_account ('phone','aid','uid','token','date') VALUES ('"+account.getPhone()+"','"+account.getAid()
                    +"','"+account.getUid()+"','"+account.getToken()+"','"+ DateUtil.getCurrentDate()+"')";
            stmt.executeUpdate(sql);
            stmt.close();
            conn.close();
            return true;
        }catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 检测账号是否存在
     * @param phone     需要检测的手机号码
     * @return
     */
    public boolean isExist(String phone){
        try {
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:"+DB_PATH);
            Statement stmt = conn.createStatement();
            String sql="SELECT  COUNT(1) FROM  tb_account WHERE  phone='"+phone+"'";
            ResultSet resultSet=stmt.executeQuery(sql);
            int count=resultSet.getInt(1);
            stmt.close();
            conn.close();
            if(count>0){
                return false;
            }else{
                return true;
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }

    /**
     *  根据时间获取一定数量的账号
     * @param num           需要获取的张混数量
     * @param date          时间条件
     * @return
     */
    public List<Account> listData(int num,String date){
        List<Account> accountList=new ArrayList<>();
        try {
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:"+DB_PATH);
            Statement stmt = conn.createStatement();
            String sql="SELECT * FROM tb_account WHERE date!='"+date+"' LIMIT "+num+";";
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()){
                Account account=new Account();
                account.setAid(rs.getString("aid"));
                account.setId(rs.getLong("id"));
                account.setPhone(rs.getString("phone"));
                account.setToken(rs.getString("token"));
                account.setUid(rs.getString("uid"));
                account.setDate(rs.getString("date"));
                accountList.add(account);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return accountList;
    }

    //更新账号信息
    public boolean updateData(Account account){
        try {
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:"+DB_PATH);
            Statement stmt = conn.createStatement();
            String sql="UPDATE tb_account SET date = '"+account.getDate()+"' WHERE id="+account.getId();
            stmt.executeUpdate(sql);
            stmt.close();
            conn.close();
            return true;
        }catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getAid() {
        return aid;
    }

    public void setAid(String aid) {
        this.aid = aid;
    }

    public String getUid() {
        return uid;
    }

    public void setUid(String uid) {
        this.uid = uid;
    }

    public String getToken() {
        return token;
    }

    public void setToken(String token) {
        this.token = token;
    }

    public String getDate() {
        return date;
    }

    public void setDate(String date) {
        this.date = date;
    }
}
